import mysql.connector

# re_status 0未,1ok,9模糊,8常识
mydb = mysql.connector.connect(
    host="192.168.47.102",  # 数据库主机地址
    user="myuser",  # 数据库用户名
    port=3307,
    database="iridescent_study",  # 数据库名称 没有的话插入操作报错
    passwd="211314wccA@",  # 数据库密码
)

mycursor = mydb.cursor()

# 插入字典表
init = "SELECT CASE WHEN re_status =0 THEN '未记' WHEN re_status =1 THEN 'ok' WHEN re_status =9 THEN '模糊' WHEN re_status =8 THEN '常用'END AS STATUS , COUNT(1) as count FROM egl_world GROUP BY re_status;"
init2 = "SELECT CASE WHEN re_status =0 THEN '未记' WHEN re_status =1 THEN 'ok' WHEN re_status =9 THEN '模糊' WHEN re_status =8 THEN '常用'END AS STATUS , COUNT(1) as count FROM egl_world where group_id ={} GROUP BY re_status;"
sql = "select * from egl_world where re_status ={} and group_id ={} ORDER BY RAND() LIMIT 1"
sql2 = "select * from egl_world where re_status ={}  ORDER BY RAND() LIMIT 1"
delete_sql="delete "

update_sql = "UPDATE  egl_world SET re_status ={} WHERE id ={}"

def get_msg(group_id):
    tmp_sql = init2.format(group_id)
    mycursor.execute(tmp_sql)
    results = mycursor.fetchall()
    results_str = "";
    for row in results:
        results_str = results_str + row[0] + ":" + row[1].__str__() + "个, "
    print(results_str)
def get_msg2():
    mycursor.execute(init)
    results = mycursor.fetchall()
    results_str = "";
    for row in results:
        results_str = results_str + row[0] + ":" + row[1].__str__() + "个, "
    print(results_str)

def get_date(data_type, group_id):

    tmp_sql = sql.format(data_type, group_id)
    mycursor.execute(tmp_sql)
    results = mycursor.fetchall()
    return results[0]

def get_date2(data_type):

    tmp_sql = sql2.format(data_type, group_id)
    mycursor.execute(tmp_sql)
    results = mycursor.fetchall()
    return results[0]


def get_input(id, status):
    tmp_sql = update_sql.format(status, id)
    # print(tmp_sql)
    mycursor.execute(tmp_sql)
    mydb.commit()


# for i in range(54):
#     group_sql="UPDATE  egl_world SET group_id ="+i.__str__()+" WHERE id in ( select id from ( SELECT id from egl_world limit "+(i*100).__str__()+",100) a )"
#     print(group_sql)
#     mycursor.execute(group_sql)
#     mydb.commit()
if __name__ == '__main__':

    print("选择分组")
    group_id = input();
    if group_id:
        get_msg(group_id)
    else:
        get_msg2()
    print("选择查询类型 : 0未,1ok")
    select_type = input();

    while 1:
        word_data ={};
        if group_id:
            word_data = get_date(select_type, group_id)
        else:
            word_data = get_date2(select_type)
        print(word_data[1])
        xx = input();
        if xx=="exit":
            exit(0)
        print(word_data[2])
        xx2 = input();
        if xx2=="exit":
            exit(0)
        if xx2 != "":
            get_input(word_data[0], xx2)

# for row in results:
#     tmp_count = tmp_count + 1
#     tmp_write_str = tmp_write_str + ", 第 " + tmp_count.__str__() + "个单词";
#     tmp_write_str = tmp_write_str + row[1];
#     tmp_write_str = tmp_write_str + ",";
#     print(tmp_write_str)
# for tmp_char in row[0]:
#     # print(tmp_char)
#     tmp_write_str = tmp_write_str + tmp_char;
#     tmp_write_str = tmp_write_str + " ";
# tmp_write_str = tmp_write_str + " ,,";
# tmp_write_str = tmp_write_str + row[0];
# tmp_write_str = tmp_write_str + " ,,";
# tmp_write_str = tmp_write_str + row[1];
